﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using IHISCETeim.BLL;

using NPOI.HSSF.UserModel;
using NPOI.HPSF;
using NPOI.POIFS.FileSystem;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.HSSF.Util;
using IHISCETeim.Library;

namespace IHISCETeim.Web.Export.FTradeModel
{
    public partial class FTradeExportReport5 : ExportPageBase
    {
        protected override string xlsTemplateFileName
        {
            get
            {
                return "应收帐款_风险部逾期利息.xls";
            }
        }
        protected override string SheetName
        {
            get
            {
                return "逾期利息";
            }
        }

        protected DateTime lastDaoQiRi
        {
            get
            {
                DateTime dt = new DateTime(Year, Month, 1);

                return dt.AddMonths(1).AddDays(-1);

            }
        }

        protected int Year
        {
            get
            {
                return GetRequestValue<int>("Year", DateTime.Now.Year);
            }
        }
        protected int Month
        {
            get
            {
                return GetRequestValue<int>("Month", DateTime.Now.Month);
            }
        }

        protected decimal rate
        {
            get
            {
                return GetRequestValue<decimal>("rate", 0);
            }
        }


        protected void Page_Load(object sender, EventArgs e)
        {
            //营业经理权限 2014-02-21
            //营业经理权限 2014-02-21
            string agentList = base.FTradeAgentPowerSqlWhere(UserId);
            string wheresql = string.IsNullOrEmpty(agentList) ? " 1=1 " : string.Format(" JingXiaoShang in ({0})", agentList);
            wheresql += " and " + QueryOperatorsEnumHelper.ConvertCommonOperator("WeiShouKuanXiang", QueryOperatorsEnum.大于, "0");
            
            //所有有未收款项的经销商
            DataTable allAgentlist = FTradeReportController.GetALLJinXiaoShang(wheresql);

            //DataTable allDetail = new DataTable();//所有到期应还款
            DataTable sboagentlist = SBOController.GetAgentList();//所有经销商列表
            //
            InitializeWorkbook();

            string AliasName = "";
            int sheetindex = 1;//从第二个工作表开始
            foreach (DataRow dr in allAgentlist.Rows)
            {
                AliasName = dr["JingXiaoShang"].ToString();
                DataTable detaildt = FTradeReportController.Report5GetReportDate(rate, lastDaoQiRi, AliasName)[0];
                if (detaildt.Rows.Count <= 0)
                {
                    continue;
                }
                ISheet sheet2 = hssfworkbook.CloneSheet(0);
                hssfworkbook.SetSheetName(sheetindex, AliasName);
                //经销商全称                
                DataRow[] drs = sboagentlist.Select(string.Format(" AliasName ='{0}'", AliasName.Trim()));
                string cardname = drs[0]["CardName"].ToString();

                if (detaildt.Rows.Count <= 0)
                    continue;
                WriteSheet(sheet2, detaildt, cardname);

                sheet2.ForceFormulaRecalculation = true;
                sheetindex++;
            }
            //隐藏
            hssfworkbook.RemoveSheetAt(0);
            //
            WriteToFile();
        }

        private void WriteSheet(ISheet sheet2, DataTable exportDT, string cardname)
        {
           
            //
            sheet2.GetRow(1).GetCell(1).SetCellValue(cardname);
            sheet2.GetRow(3).GetCell(6).SetCellValue(string.Format("{0}%", rate));
            sheet2.GetRow(0).GetCell(0).SetCellValue(string.Format("{0}年逾期利息、返利扣罚确认书", Year));
            sheet2.GetRow(5).GetCell(0).SetCellValue(string.Format("{0}年货款逾期利息明细：",Year));
            sheet2.GetRow(14).GetCell(0).SetCellValue(string.Format("{0}年样机逾期利息明细：", Year));
            sheet2.GetRow(20).GetCell(0).SetCellValue(string.Format("{0}年因融资租赁逾期致返利扣罚明细：", Year));
            sheet2.GetRow(24).GetCell(0).SetCellValue(string.Format("        请于{0}前确认回传,以上如有疑议，请和我司风险管理部联系（联系人：蔡巧燕，电话：0592-6292270,传真:0592-6292257）。 ", lastDaoQiRi.ToString("yyyy-MM-25")));
            sheet2.GetRow(27).GetCell(6).SetCellValue(DateTime.Now.ToString("yyyy-MM-dd"));
            //第12行 处理
            ////截止2013年07月09日,贵司整机欠款总额为6.0512万元(明细见附表),其中本月应还款总额为6.0512万元,提醒贵司到期按时还款,以免影响信用评估。
            //string WeiShouKuanXiang = Convert.ToDecimal(exportDT.Compute("SUM(WeiShouKuanXiang)", "")).ToString("N4");
            //string daoqijine = Convert.ToDecimal(exportDT.Compute("SUM(daoqijine)", "")).ToString("N4");
            ////
            //ICell richTextCell = sheet2.GetRow(11).GetCell(0);
            //string richText = string.Format("       截止{0},贵司整机欠款总额为{1}万元(明细见附表),其中本月应还款总额为{2}万元,提醒贵司到期按时还款,以免影响信用评估", CurrDate, WeiShouKuanXiang, daoqijine);
            //HSSFRichTextString richtextObj = new HSSFRichTextString(richText);
            //IFont fontStyleRich = hssfworkbook.CreateFont();
            //fontStyleRich.Underline = (byte)FontUnderlineType.SINGLE;
            //fontStyleRich.Boldweight = short.MaxValue;//加粗
            //fontStyleRich.FontHeightInPoints = 12;
            ////12号字体样式
            //IFont fontStyleRich2 = hssfworkbook.CreateFont();
            //fontStyleRich2.FontHeightInPoints = 12;

            //richtextObj.ApplyFont(0, richText.Length, fontStyleRich2);
            ////1.加粗位置处理
            //int boldStart = string.Format("       截止{0},贵司整机欠款总额为", CurrDate).Length; ;
            //int boldEnd = string.Format("       截止{0},贵司整机欠款总额为{1}万元", CurrDate, WeiShouKuanXiang).Length;
            //richtextObj.ApplyFont(boldStart, boldEnd, fontStyleRich);
            //boldStart = string.Format("       截止{0},贵司整机欠款总额为{1}万元(明细见附表),", CurrDate, WeiShouKuanXiang).Length;
            //boldEnd = string.Format("       截止{0},贵司整机欠款总额为{1}万元(明细见附表),其中本月应还款总额为{2}万元,提醒贵司到期按时还款", CurrDate, WeiShouKuanXiang, daoqijine).Length;
            //richtextObj.ApplyFont(boldStart, boldEnd, fontStyleRich);
            //richTextCell.SetCellValue(richtextObj);
            ////HSSFRichTextString
            ////STRING:附表：整机欠款明细（截止2013年07月09日）：
            //richText = string.Format("附表：整机欠款明细（截止{0}）", CurrDate);
            ////ICellStyle style12 = hssfworkbook.CreateCellStyle();
            ////style12.SetFont(fontStyleRich2);
            //ICell cell1 = sheet2.GetRow(12).GetCell(0); ;
            ////cell1.CellStyle = style12;
            //cell1.SetCellValue(richText);
            ////列标题
            //sheet2.GetRow(13).GetCell(3).SetCellValue(string.Format("贵司{0}月份应还款", Convert.ToDateTime(DateTo).ToString("MM")));
            ////设置其它信息
            //sheet2.GetRow(20).GetCell(4).SetCellValue(CurrDate);
            //sheet2.GetRow(21).GetCell(0).SetCellValue(string.Format("-------------------------请填下表于{0}月25日前回传：0592-6292200----------------------", Convert.ToDateTime(DateTo).ToString("MM")));
            //sheet2.GetRow(22).GetCell(0).SetCellValue(string.Format("{0}份还款计划回复", Convert.ToDateTime(DateTo).ToString("yyyy年MM月")));
            //sheet2.GetRow(27).GetCell(4).SetCellValue(string.Format("预计付款日：  {0}月   日", Convert.ToDateTime(DateTo).ToString("MM")));
            //sheet2.GetRow(29).GetCell(0).SetCellValue(string.Format("{0}（盖章）", cardname));
            //sheet2.GetRow(30).GetCell(4).SetCellValue(string.Format("{0}   日", Convert.ToDateTime(DateTo).ToString("yyyy年MM月")));
            //开始FOR各合同信息
            if(exportDT.Rows.Count>4)
            {
                HSSFRow 源格式行 = (HSSFRow)sheet2.GetRow(7);
                MyInsertRow((HSSFSheet)sheet2, 8, exportDT.Rows.Count - 4, 源格式行);
            }
            int rowindexBase = 7;
            int rowindex = 0;
            int rowReal = rowindex + rowindexBase;
            //ICellStyle style12 = hssfworkbook.CreateCellStyle();
            //style12.SetFont(fontStyleRich2);
            ////水平对齐  
            //style12.Alignment = NPOI.SS.UserModel.HorizontalAlignment.LEFT;
            ////垂直对齐  
            //style12.VerticalAlignment = VerticalAlignment.CENTER;
            ////自动换行  
            //style12.WrapText = true;

            foreach (DataRow dr in exportDT.Rows)
            {
                HSSFRow row1 = (HSSFRow)sheet2.GetRow(rowReal);
                //合并处理
                //  sheet2.AddMergedRegion(new CellRangeAddress(rowReal, rowReal, 4, 5));
                // row1.GetCell(4).CellStyle = style12;

                row1.GetCell(0).SetCellValue(dr["TongJiMonth"].ToString());
                row1.GetCell(1).SetCellValue(dr["HeTongHao"].ToString());
                row1.GetCell(2).SetCellValue(dr["JiHao"].ToString());
                row1.GetCell(3).SetCellValue(Convert.ToDouble(dr["WeiShouKuanXiang"]));
                row1.GetCell(4).SetCellValue(Convert.ToDateTime( dr["DaoQiRi"]).ToString("yyyy-MM-dd"));
                row1.GetCell(5).SetCellValue(Convert.ToDouble(dr["yiqijine"]));
                row1.GetCell(6).SetCellValue(Convert.ToDouble(dr["accrualDay"]));
                row1.GetCell(7).SetCellValue(Convert.ToDouble(dr["yiQiRate"]));
                row1.GetCell(8).SetCellValue(dr["XiaoShouLeiXing"].ToString());
                row1.GetCell(9).SetCellValue(dr["ZDXiaoShouState"].ToString());
                row1.GetCell(10).SetCellValue(dr["ZDXiaoShouShiJian"].ToString());
                rowindex++;
                rowReal = rowindex + rowindexBase;
            }

        }

        protected override string GetXlsFileName(string oldFileName)
        {
            //return base.GetXlsFileName(oldFileName);

            return string.Format("{0}年{1}月{2}.xls", Year,Month, "逾期利息汇总表").Trim();
        }

    }
}